{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f0032298",
   "metadata": {},
   "source": [
    "# Анализ данных об инвестициях с помощью PostgreSQL"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be0a0ce6",
   "metadata": {},
   "source": [
    "Анализ данных об инвестиционных фондах и стартапах, в которые они инвестировали с 2010 по 2013 годы в разных странах. Анализ произведен с помощью PostgreSQL."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e387b9c",
   "metadata": {
    "toc": true
   },
   "source": [
    "<h1>Содержание<span class=\"tocSkip\"></span></h1>\n",
    "<div class=\"toc\"><ul class=\"toc-item\"><li><span><a href=\"#Анализ-данных-о-закрытых-компаниях-в-таблице-company\" data-toc-modified-id=\"Анализ-данных-о-закрытых-компаниях-в-таблице-company-1\"><span class=\"toc-item-num\">1&nbsp;&nbsp;</span>Анализ данных о закрытых компаниях в таблице <code>company</code></a></span></li><li><span><a href=\"#Количество-привлеченных-средств-для-новостных-компаний-США\" data-toc-modified-id=\"Количество-привлеченных-средств-для-новостных-компаний-США-2\"><span class=\"toc-item-num\">2&nbsp;&nbsp;</span>Количество привлеченных средств для новостных компаний США</a></span></li><li><span><a href=\"#Компании,-купленные-за-наличные-с-2011-по-2013-год\" data-toc-modified-id=\"Компании,-купленные-за-наличные-с-2011-по-2013-год-3\"><span class=\"toc-item-num\">3&nbsp;&nbsp;</span>Компании, купленные за наличные с 2011 по 2013 год</a></span></li><li><span><a href=\"#Информация-о-сотрудниках-компаний\" data-toc-modified-id=\"Информация-о-сотрудниках-компаний-4\"><span class=\"toc-item-num\">4&nbsp;&nbsp;</span>Информация о сотрудниках компаний</a></span></li><li><span><a href=\"#Колиество-привлеченных-инвестиций-компаниями-по-странам\" data-toc-modified-id=\"Колиество-привлеченных-инвестиций-компаниями-по-странам-5\"><span class=\"toc-item-num\">5&nbsp;&nbsp;</span>Колиество привлеченных инвестиций компаниями по странам</a></span></li><li><span><a href=\"#Минимальные-и-максимальные-суммы-привлеченных-инвестиций-по-датам\" data-toc-modified-id=\"Минимальные-и-максимальные-суммы-привлеченных-инвестиций-по-датам-6\"><span class=\"toc-item-num\">6&nbsp;&nbsp;</span>Минимальные и максимальные суммы привлеченных инвестиций по датам</a></span></li><li><span><a href=\"#Категоризация-фонодов-по-количеству-инвестиций\" data-toc-modified-id=\"Категоризация-фонодов-по-количеству-инвестиций-7\"><span class=\"toc-item-num\">7&nbsp;&nbsp;</span>Категоризация фонодов по количеству инвестиций</a></span></li><li><span><a href=\"#ТОП-10-стран,-которые-чаще-всего-инвестируют-в-стартапы\" data-toc-modified-id=\"ТОП-10-стран,-которые-чаще-всего-инвестируют-в-стартапы-8\"><span class=\"toc-item-num\">8&nbsp;&nbsp;</span>ТОП-10 стран, которые чаще всего инвестируют в стартапы</a></span></li><li><span><a href=\"#Сотрудники-стартапов-и-их-образование\" data-toc-modified-id=\"Сотрудники-стартапов-и-их-образование-9\"><span class=\"toc-item-num\">9&nbsp;&nbsp;</span>Сотрудники стартапов и их образование</a></span></li><li><span><a href=\"#Компании-аутсайдеры\" data-toc-modified-id=\"Компании-аутсайдеры-10\"><span class=\"toc-item-num\">10&nbsp;&nbsp;</span>Компании аутсайдеры</a></span></li><li><span><a href=\"#Сотрудники-компаний-аутсайдеров\" data-toc-modified-id=\"Сотрудники-компаний-аутсайдеров-11\"><span class=\"toc-item-num\">11&nbsp;&nbsp;</span>Сотрудники компаний аутсайдеров</a></span></li><li><span><a href=\"#Компании,-прошедшие-больше-шести-важных-раундов-финансирования-с-2012-по-2013-год\" data-toc-modified-id=\"Компании,-прошедшие-больше-шести-важных-раундов-финансирования-с-2012-по-2013-год-12\"><span class=\"toc-item-num\">12&nbsp;&nbsp;</span>Компании, прошедшие больше шести важных раундов финансирования с 2012 по 2013 год</a></span></li><li><span><a href=\"#Во-сколько-раз-сумма-покупки-превысила-сумму-вложений\" data-toc-modified-id=\"Во-сколько-раз-сумма-покупки-превысила-сумму-вложений-13\"><span class=\"toc-item-num\">13&nbsp;&nbsp;</span>Во сколько раз сумма покупки превысила сумму вложений</a></span></li><li><span><a href=\"#Компаний-из-категории-social,-получившие-финансирование-с-2010-по-2013-год\" data-toc-modified-id=\"Компаний-из-категории-social,-получившие-финансирование-с-2010-по-2013-год-14\"><span class=\"toc-item-num\">14&nbsp;&nbsp;</span>Компаний из категории <code>social</code>, получившие финансирование с 2010 по 2013 год</a></span></li><li><span><a href=\"#Данные-об-инвестиционных-раундах-по-мусяцам\" data-toc-modified-id=\"Данные-об-инвестиционных-раундах-по-мусяцам-15\"><span class=\"toc-item-num\">15&nbsp;&nbsp;</span>Данные об инвестиционных раундах по мусяцам</a></span></li><li><span><a href=\"#Сумма-инвестиций-в-разные-страны-по-годам\" data-toc-modified-id=\"Сумма-инвестиций-в-разные-страны-по-годам-16\"><span class=\"toc-item-num\">16&nbsp;&nbsp;</span>Сумма инвестиций в разные страны по годам</a></span></li></ul></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "918af6c9",
   "metadata": {},
   "source": [
    "## Анализ данных о закрытых компаниях в таблице `company`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1bf4ce26",
   "metadata": {},
   "source": [
    "Отобразите 5 первых записей из таблицы `company` по компаниям, которые закрылись."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88943600",
   "metadata": {},
   "source": [
    "```sql\n",
    "SELECT *\n",
    "FROM   company\n",
    "WHERE  status = 'closed';\n",
    "LIMIT  5;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5080b9d2",
   "metadata": {},
   "source": [
    "| id | name | category_code | status | founded_at | closed_at | domain | twitter_username | country_code | investment_rounds | funding_rounds | funding_total | milestones | created_at | updated_at |\n",
    "|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|\n",
    "| 10012 | moviestring.com | games_video | closed | 2008-08-22 | 2010-01-01 | moviestring.com |  |  | 0 | 0 | 0 | 0 | 2008-08-24 23:07:26 | 2010-10-01 00:12:22 |\n",
    "| 1006 | Youlicit | web | closed | 2006-09-01 | 2010-05-22 | youlicit.com | youlicit | USA | 0 | 1 | 0 | 0 | 2007-10-02 09:45:00 | 2013-10-19 10:39:54 |\n",
    "| 10062 | Workstir | web | closed |  | 2011-12-17 | workstir.com |  | USA | 0 | 1 | 0 | 2 | 2008-08-25 18:26:25 | 2013-10-16 09:57:06 |\n",
    "| 10092 | FairSoftware | web | closed | 2007-01-01 | 2012-07-24 | fairsoftware.net |  | USA | 0 | 1 | 100000 | 1 | 2008-08-25 23:41:44 | 2013-10-15 04:33:46 |\n",
    "| 101 | SellABand | games_video | closed | 2006-08-01 | 2010-02-23 | sellaband.com | sellaband | DEU | 0 | 1 | 5e+06 | 5 | 2007-07-04 05:29:56 | 2010-04-29 21:39:39 |\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9338b211",
   "metadata": {},
   "source": [
    "## Количество привлеченных средств для новостных компаний США"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "540bc467",
   "metadata": {},
   "source": [
    "Отобразите количество привлечённых средств для новостных компаний США. Используйте данные из таблицы `company`. Отсортируйте первых пять значений по убыванию значений в поле `funding_total`."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "73c5e766",
   "metadata": {},
   "source": [
    "```sql\n",
    "SELECT   funding_total\n",
    "FROM     company\n",
    "WHERE    category_code = 'news'\n",
    "AND      country_code = 'USA'\n",
    "ORDER BY funding_total DESC\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d10a9b12",
   "metadata": {},
   "source": [
    "| funding_total |\n",
    "|---|\n",
    "| 6.22553e+08 |\n",
    "| 2.5e+08 |\n",
    "| 1.605e+08 |\n",
    "| 1.28e+08 |\n",
    "| 1.265e+08 |\n",
    "| ... |\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a53ef778",
   "metadata": {},
   "source": [
    "## Компании, купленные за наличные с 2011 по 2013 год"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9e858531",
   "metadata": {},
   "source": [
    "Найдите общую сумму сделок по покупке одних компаний другими в долларах. Отберите сделки, которые осуществлялись только за наличные с 2011 по 2013 год включительно."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "44bcf943",
   "metadata": {},
   "source": [
    "```sql\n",
    "SELECT SUM(price_amount)\n",
    "FROM   acquisition\n",
    "WHERE  term_code = 'cash'\n",
    "AND    EXTRACT(YEAR FROM acquired_at) IN (2011, 2012, 2013);\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a4c5683",
   "metadata": {},
   "source": [
    "| sum |\n",
    "|---|\n",
    "| 1.37762e+11 |\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a79d5ce1",
   "metadata": {},
   "source": [
    "## Информация о сотрудниках компаний"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f4584b16",
   "metadata": {},
   "source": [
    "Отобразите имя, фамилию и названия аккаунтов людей в твиттере, у которых названия аккаунтов начинаются на `Silver`."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1f0d0f25",
   "metadata": {},
   "source": [
    "```sql\n",
    "SELECT first_name, \n",
    "       last_name, \n",
    "       twitter_username\n",
    "FROM   people\n",
    "WHERE  twitter_username LIKE 'Silver%';\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "72245fb6",
   "metadata": {},
   "source": [
    "| first_name | last_name | twitter_username |\n",
    "|---|---|---|\n",
    "| Rebecca | Silver | SilverRebecca |\n",
    "| Silver | Teede | SilverMatrixx |\n",
    "| Mattias | Guilotte | Silverreven |"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "836fd655",
   "metadata": {},
   "source": [
    "Выведите на экран всю информацию о людях, у которых названия аккаунтов в твиттере содержат подстроку `money`, а фамилия начинается на `K`."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "34b96cd5",
   "metadata": {},
   "source": [
    "```sql\n",
    "SELECT *\n",
    "FROM   people\n",
    "WHERE  twitter_username LIKE '%money%'\n",
    "AND    last_name LIKE 'K%';\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "704ad5ae",
   "metadata": {},
   "source": [
    "| id | first_name | last_name | company_id | twitter_username | created_at | updated_at |\n",
    "|---|---|---|---|---|---|---|\n",
    "| 63081 | Gregory | Kim |  | gmoney75 | 2010-07-13 03:46:28 | 2011-12-12 22:01:34 |\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "781f253f",
   "metadata": {},
   "source": [
    "## Колиество привлеченных инвестиций компаниями по странам"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb0d3c99",
   "metadata": {},
   "source": [
    "Для каждой страны отобразите общую сумму привлечённых инвестиций, которые получили компании, зарегистрированные в этой стране. Страну, в которой зарегистрирована компания, можно определить по коду страны. Отсортируйте данные по убыванию суммы."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4b8ba992",
   "metadata": {},
   "source": [
    "```sql\n",
    "SELECT country_code, \n",
    "       SUM(funding_total)\n",
    "FROM   company\n",
    "GROUP BY country_code\n",
    "ORDER BY SUM(funding_total) DESC\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8b8dbbea",
   "metadata": {},
   "source": [
    "| country_code | sum |\n",
    "|---|---|\n",
    "| USA | 3.10588e+11 |\n",
    "| GBR | 1.77056e+10 |\n",
    "|  | 1.08559e+10\n",
    "| CHN | 1.06897e+10 |\n",
    "| CAN | 9.86636e+09 |\n",
    "| IND | 6.14141e+09 |\n",
    "| DEU | 5.76577e+09 |\n",
    "| FRA | 4.59514e+09 |\n",
    "| ISR | 4.48009e+09 |\n",
    "| CHE | 2.82925e+09 |\n",
    "| NLD | 2.35397e+09 |\n",
    "| RUS | 2.29394e+09 |\n",
    "| ESP | 2.13734e+09 |\n",
    "| IRL | 2.08067e+09 |\n",
    "| AUS | 1.67241e+09 |\n",
    "| JPN | 1.62262e+09 |\n",
    "| SWE | 1.60811e+09 |\n",
    "| MYS | 1.52436e+09 |\n",
    "| LUX | 1.11175e+09 |\n",
    "| BRA | 1.1036e+09 |\n",
    "| ... | ... |\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "74ed8382",
   "metadata": {},
   "source": [
    "## Минимальные и максимальные суммы привлеченных инвестиций по датам"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "994f84d3",
   "metadata": {},
   "source": [
    "Составьте таблицу, в которую войдёт дата проведения раунда, а также минимальное и максимальное значения суммы инвестиций, привлечённых в эту дату. Оставьте в итоговой таблице только те первых пять записей, в которых минимальное значение суммы инвестиций не равно нулю и не равно максимальному значению."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1589c6d7",
   "metadata": {},
   "source": [
    "```sql\n",
    "SELECT funded_at, \n",
    "       MIN(raised_amount), \n",
    "       MAX(raised_amount)\n",
    "FROM   funding_round\n",
    "GROUP BY funded_at\n",
    "HAVING   MIN(raised_amount) > 0\n",
    "AND      MIN(raised_amount) != MAX(raised_amount)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4710e2d9",
   "metadata": {},
   "source": [
    "| funded_at | min | max |\n",
    "|---|---|---|\n",
    "| 2012-08-22 | 40000 | 7.5e+07 |\n",
    "| 2010-07-25 | 3.27825e+06 | 9e+06 |\n",
    "| 2002-03-01 | 2.84418e+06 | 8.95915e+06 |\n",
    "| 2010-10-11 | 28000 | 2e+08 |\n",
    "| 2007-01-18 | 5e+06 | 2.3e+07 |\n",
    "| ... | ... | ... |\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "89c892d8",
   "metadata": {},
   "source": [
    "## Категоризация фонодов по количеству инвестиций"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ac857fb",
   "metadata": {},
   "source": [
    "Создайте поле с категориями:\n",
    "- Для фондов, которые инвестируют в 100 и более компаний, назначьте категорию `high_activity`.\n",
    "- Для фондов, которые инвестируют в 20 и более компаний до 100, назначьте категорию `middle_activity`.\n",
    "- Если количество инвестируемых компаний фонда не достигает 20, назначьте категорию `low_activity`.\n",
    "\n",
    "Отобразите все поля таблицы `fund` и новое поле с категориями для первых пяти записей."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b6b09608",
   "metadata": {},
   "source": [
    "```sql\n",
    "SELECT *, \n",
    "\t   CASE \n",
    "\t\t\tWHEN invested_companies >= 100 THEN 'high_activity' \n",
    "\t\t\tWHEN invested_companies >= 20 THEN 'middle_activity' \n",
    "\t\t\tELSE 'low_activity' \n",
    "\t   END \n",
    "FROM   fund\n",
    "LIMIT  5;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f5a1ef0",
   "metadata": {},
   "source": [
    "| id | name | founded_at | domain | twitter_username | country_code | investment_rounds | invested_companies | milestones | created_at | updated_at | case |\n",
    "|---|---|---|---|---|---|---|---|---|---|---|---|\n",
    "| 13131 |  |  |  |  |  | 0 | 0 | 0 | 2013-08-19 18:46:55 | 2013-08-19 19:55:07 | low_activity |\n",
    "| 1 | Greylock Partners | 1965-01-01 | greylock.com | greylockvc | USA | 307 | 196 | 0 | 2007-05-25 20:18:23 | 2012-12-27 00:42:24 | high_activity |\n",
    "| 10 | Mission Ventures | 1996-01-01 | missionventures.com |  | USA | 58 | 33 | 0 | 2007-06-05 05:24:58 | 2013-10-10 22:06:31 | middle_activity |\n",
    "| 100 | Kapor Enterprises, Inc. |  | kei.com |  | USA | 2 | 1 | 0 | 2007-07-12 09:42:21 | 2008-11-21 05:41:53 | low_activity |\n",
    "| 1000 | Speed Ventures |  |  |  |  | 0 | 0 | 1 | 2008-04-13 23:52:27 | 2008-12-10 09:37:18 | low_activity |\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "07123ece",
   "metadata": {},
   "source": [
    "Для каждой из категорий, назначенных в предыдущем задании, посчитайте округлённое до ближайшего целого числа среднее количество инвестиционных раундов, в которых фонд принимал участие. Выведите на экран категории и среднее число инвестиционных раундов. Отсортируйте таблицу по возрастанию среднего."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee0f2443",
   "metadata": {},
   "source": [
    "```sql\n",
    "SELECT CASE\n",
    "           WHEN invested_companies>=100 THEN 'high_activity'\n",
    "           WHEN invested_companies>=20 THEN 'middle_activity'\n",
    "           ELSE 'low_activity'\n",
    "       END AS activity, \n",
    "       ROUND(AVG(investment_rounds)) \n",
    "FROM     fund\n",
    "GROUP BY activity\n",
    "ORDER BY ROUND(AVG(investment_rounds));\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "434a8375",
   "metadata": {},
   "source": [
    "| activity | round |\n",
    "|---|---|\n",
    "| low_activity | 2 |\n",
    "| middle_activity | 51 |\n",
    "| high_activity | 252 |\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88d1ab30",
   "metadata": {},
   "source": [
    "## ТОП-10 стран, которые чаще всего инвестируют в стартапы"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4cf30d33",
   "metadata": {},
   "source": [
    "Проанализируйте, в каких странах находятся фонды, которые чаще всего инвестируют в стартапы. \n",
    "\n",
    "Для каждой страны посчитайте минимальное, максимальное и среднее число компаний, в которые инвестировали фонды этой страны, основанные с 2010 по 2012 год включительно. Исключите страны с фондами, у которых минимальное число компаний, получивших инвестиции, равно нулю. \n",
    "\n",
    "Выгрузите десять самых активных стран-инвесторов: отсортируйте таблицу по среднему количеству компаний от большего к меньшему. Затем добавьте сортировку по коду страны в лексикографическом порядке."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "982fd94a",
   "metadata": {},
   "source": [
    "```sql\n",
    "SELECT country_code, \n",
    "   \t   MIN(invested_companies), \n",
    "   \t   MAX(invested_companies), \n",
    "   \t   AVG(invested_companies)\n",
    "FROM   fund\n",
    "WHERE  EXTRACT(YEAR FROM founded_at) BETWEEN 2010 AND 2012\n",
    "GROUP BY country_code\n",
    "HAVING   MIN(invested_companies) > 0\n",
    "ORDER BY AVG(invested_companies) DESC, \n",
    "         country_code\n",
    "LIMIT  10;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2cfdf7fa",
   "metadata": {},
   "source": [
    "| country_code | min | max | avg |\n",
    "|---|---|---|---|\n",
    "| BGR | 25 | 35 | 30 | \n",
    "| CHL | 29 | 29 | 29 |\n",
    "| UKR | 8 | 10 | 9 |\n",
    "| LTU | 5 | 5 | 5 |\n",
    "| IRL | 4 | 5 | 4.5 |\n",
    "| KEN | 3 | 3 | 3 |\n",
    "| LBN | 3 | 3 | 3 |\n",
    "| MUS | 3 | 3 | 3 |\n",
    "| JPN | 1 | 6 | 2.83333 |\n",
    "| HKG | 2 | 3 | 2.66667 |\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8f758ff6",
   "metadata": {},
   "source": [
    "## Сотрудники стартапов и их образование"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9f156ed3",
   "metadata": {},
   "source": [
    "Отобразите имя и фамилию всех сотрудников стартапов. Добавьте поле с названием учебного заведения, которое окончил сотрудник, если эта информация известна."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "42e45de1",
   "metadata": {},
   "source": [
    "```sql\n",
    "SELECT p.first_name, \n",
    "       p.last_name, \n",
    "       e.instituition\n",
    "FROM   people AS p\n",
    "LEFT JOIN education AS e ON e.person_id = p.id\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d9ee8d4",
   "metadata": {},
   "source": [
    "| first_name | last_name | instituition |\n",
    "|---|---|---|\n",
    "| John | Green | Washington University, St. Louis |\n",
    "| John | Green | Boston University |\n",
    "| David | Peters | Rice University |\n",
    "| Dan | Birdwhistell | University of Cambridge |\n",
    "| Gal | Cohen | Tel Aviv University |\n",
    "| ... | ... | ... |"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f7d9dce5",
   "metadata": {},
   "source": [
    "Для каждой компании найдите количество учебных заведений, которые окончили её сотрудники. Выведите название компании и число уникальных названий учебных заведений. Составьте топ-5 компаний по количеству университетов."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ab7ea429",
   "metadata": {},
   "source": [
    "```sql\n",
    "WITH \n",
    "pe AS (SELECT p.company_id,\n",
    "              e.instituition\n",
    "       FROM   people AS p\n",
    "       JOIN   education AS e ON e.person_id = p.id)\n",
    "\n",
    "SELECT c.name, \n",
    "       COUNT(DISTINCT pe.instituition)\n",
    "FROM   company AS c\n",
    "JOIN   pe ON company_id = c.id\n",
    "GROUP BY c.name\n",
    "ORDER BY COUNT(DISTINCT pe.instituition) DESC\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "04f9961c",
   "metadata": {},
   "source": [
    "| name | count |\n",
    "|---|---|\n",
    "| Google | 167 |\n",
    "| Yahoo! | 115 |\n",
    "| Microsoft | 111 |\n",
    "| Knight Foundation | 74 |\n",
    "| Comcast | 66 |\n",
    "| ... | ... |"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4397216e",
   "metadata": {},
   "source": [
    "## Компании аутсайдеры"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "96662988",
   "metadata": {},
   "source": [
    "Составьте список с уникальными названиями закрытых компаний, для которых первый раунд финансирования оказался последним."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4d4570cc",
   "metadata": {},
   "source": [
    "```sql\n",
    "WITH \n",
    "fr AS (SELECT company_id\n",
    "       FROM   funding_round\n",
    "       WHERE  is_first_round = 1\n",
    "       AND    is_last_round = 1)\n",
    "\n",
    "\n",
    "SELECT DISTINCT c.name\n",
    "FROM   company AS c\n",
    "JOIN   fr ON fr.company_id = c.id\n",
    "WHERE  c.status = 'closed'\n",
    "LIMIT  5;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "17048651",
   "metadata": {},
   "source": [
    "| name |\n",
    "|---|\n",
    "| 10BestThings |\n",
    "| 11i Solutions |\n",
    "| 169 ST. |\n",
    "| 1bib |\n",
    "| 1Cast |\n",
    "| ... |"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a2e328a3",
   "metadata": {},
   "source": [
    "## Сотрудники компаний аутсайдеров"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "52a7d824",
   "metadata": {},
   "source": [
    "Составьте список уникальных номеров сотрудников, которые работают в компаниях, отобранных в предыдущем задании."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "27534a2f",
   "metadata": {},
   "source": [
    "```sql\n",
    "WITH \n",
    "fr AS (SELECT company_id\n",
    "       FROM   funding_round\n",
    "       WHERE  is_first_round = 1\n",
    "       AND    is_last_round = 1) \n",
    "\n",
    "SELECT DISTINCT p.id\n",
    "FROM   people AS p\n",
    "WHERE  p.company_id IN (SELECT DISTINCT c.id\n",
    "\t\t\t\t\t\tFROM   company AS c\n",
    "\t\t\t\t\t\tJOIN   fr ON fr.company_id = c.id\n",
    "\t\t\t\t\t\tWHERE  c.status = 'closed') \n",
    "LIMIT  5;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "31f59fc6",
   "metadata": {},
   "source": [
    "| id |\n",
    "|---|\n",
    "| 62 |\n",
    "| 97 |\n",
    "| 98 |\n",
    "| 225 |\n",
    "| 226 |\n",
    "| ... |"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e052be3",
   "metadata": {},
   "source": [
    "Составьте таблицу, куда войдут уникальные пары с номерами сотрудников из предыдущей задачи и учебным заведением, которое окончил сотрудник."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bf737b0a",
   "metadata": {},
   "source": [
    "```sql\n",
    "WITH \n",
    "fr AS (SELECT company_id\n",
    "       FROM   funding_round\n",
    "       WHERE  is_first_round = 1\n",
    "       AND    is_last_round = 1), \n",
    "\n",
    "p AS (SELECT DISTINCT p.id\n",
    "\t  FROM   people AS p\n",
    "\t  WHERE  p.company_id IN (SELECT DISTINCT c.id\n",
    "\t\t\t\t\t\t\t  FROM   company AS c\n",
    "\t\t\t\t\t\t\t  JOIN   fr ON fr.company_id = c.id\n",
    "\t\t\t\t\t\t\t  WHERE  c.status = 'closed')) \n",
    "\n",
    "SELECT DISTINCT p.id,\n",
    "       e.instituition\n",
    "FROM   education AS e\n",
    "JOIN   p ON p.id = e.person_id\n",
    "LIMIT  5;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f3d6d22c",
   "metadata": {},
   "source": [
    "| id | instituition |\n",
    "|---|---|\n",
    "| 349 | AKI |\n",
    "| 349 | ArtEZ Hogeschool voor de Kunsten |\n",
    "| 349 | Rijks Akademie |\n",
    "| 699 | Imperial College |\n",
    "| 779 | Harvard University |\n",
    "| ... | ... |"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "04c2fdf8",
   "metadata": {},
   "source": [
    "Посчитайте количество учебных заведений для каждого сотрудника из предыдущего задания. При подсчёте учитывайте, что некоторые сотрудники могли окончить одно и то же заведение дважды."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3fc42218",
   "metadata": {},
   "source": [
    "```sql\n",
    "WITH \n",
    "fr AS (SELECT company_id\n",
    "       FROM   funding_round\n",
    "       WHERE  is_first_round = 1\n",
    "       AND    is_last_round = 1), \n",
    "\n",
    "p AS (SELECT DISTINCT p.id\n",
    "\t  FROM   people AS p\n",
    "\t  WHERE  p.company_id IN (SELECT DISTINCT c.id\n",
    "\t\t\t\t\t\t\t  FROM   company AS c\n",
    "\t\t\t\t\t\t\t  JOIN   fr ON fr.company_id = c.id\n",
    "\t\t\t\t\t\t\t  WHERE  c.status = 'closed')) \n",
    "\n",
    "SELECT p.id,\n",
    "       COUNT(e.instituition) \n",
    "FROM   education AS e\n",
    "JOIN   p ON p.id = e.person_id\n",
    "GROUP BY p.id\n",
    "LIMIT  5;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1726c6a0",
   "metadata": {},
   "source": [
    "| id | count |\n",
    "|---|---|\n",
    "| 349 | 3 |\n",
    "| 699 | 1 |\n",
    "| 779 | 2 |\n",
    "| 968 | 1 |\n",
    "| 972 | 1 |\n",
    "| ... | ... |"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd952f33",
   "metadata": {},
   "source": [
    "Дополните предыдущий запрос и выведите среднее число учебных заведений (всех, не только уникальных), которые окончили сотрудники разных компаний. Нужно вывести только одну запись, группировка здесь не понадобится."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "716e3cbd",
   "metadata": {},
   "source": [
    "```sql\n",
    "WITH \n",
    "fr AS (SELECT company_id\n",
    "       FROM   funding_round\n",
    "       WHERE  is_first_round = 1\n",
    "       AND    is_last_round = 1), \n",
    "\n",
    "p AS (SELECT DISTINCT p.id\n",
    "      FROM   people AS p\n",
    "      WHERE  p.company_id IN (SELECT DISTINCT c.id\n",
    "\t\t\t\t\t\t\t  FROM   company AS c\n",
    "\t\t\t\t\t\t\t  JOIN   fr ON fr.company_id = c.id\n",
    "\t\t\t\t\t\t\t  WHERE  c.status = 'closed')), \n",
    "\n",
    "pe AS (SELECT p.id,\n",
    "              COUNT(e.instituition)\n",
    "       FROM   education AS e\n",
    "       JOIN   p ON p.id = e.person_id\n",
    "       GROUP BY p.id) \n",
    "\n",
    "SELECT AVG(pe.count)\n",
    "FROM   pe;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "78892c47",
   "metadata": {},
   "source": [
    "| avg |\n",
    "|---|\n",
    "| 1.41509 |"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4d03842a",
   "metadata": {},
   "source": [
    "Напишите похожий запрос: выведите среднее число учебных заведений (всех, не только уникальных), которые окончили сотрудники Socialnet."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6380c297",
   "metadata": {},
   "source": [
    "```sql\n",
    "WITH \n",
    "fr AS (SELECT company_id\n",
    "       FROM   funding_round), \n",
    "\n",
    "p AS (SELECT DISTINCT p.id\n",
    "      FROM   people AS p\n",
    "      WHERE  p.company_id IN (SELECT DISTINCT c.id\n",
    "\t\t\t\t\t\t\t  FROM   company AS c\n",
    "\t\t\t\t\t\t\t  JOIN   fr ON fr.company_id = c.id\n",
    "                              WHERE  c.name LIKE '%Socialnet%')), \n",
    "\n",
    "pe AS (SELECT p.id,\n",
    "              COUNT(e.instituition)\n",
    "       FROM   education AS e\n",
    "       JOIN   p ON p.id = e.person_id\n",
    "       GROUP BY p.id) \n",
    "\n",
    "SELECT AVG(pe.count)\n",
    "FROM   pe;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "805b3093",
   "metadata": {},
   "source": [
    "| avg |\n",
    "|---|\n",
    "| 1.51111 |"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c72e6d9",
   "metadata": {},
   "source": [
    "## Компании, прошедшие больше шести важных раундов финансирования с 2012 по 2013 год"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6647e99f",
   "metadata": {},
   "source": [
    "Составьте таблицу из полей:\n",
    "\n",
    "- `name_of_fund` — название фонда;\n",
    "- `name_of_company` — название компании;\n",
    "- `amount` — сумма инвестиций, которую привлекла компания в раунде.\n",
    "\n",
    "В таблицу войдут данные о компаниях, в истории которых было больше шести важных этапов, а раунды финансирования проходили с 2012 по 2013 год включительно."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "636a576d",
   "metadata": {},
   "source": [
    "```sql\n",
    "WITH\n",
    "fr AS (SELECT *\n",
    "       FROM   funding_round AS fr\n",
    "       WHERE  funded_at BETWEEN '2012-01-01' AND '2013-12-31'), \n",
    "\n",
    "c AS (SELECT *\n",
    "      FROM   company\n",
    "      WHERE  milestones > 6)\n",
    "\n",
    "SELECT f.name AS name_of_fund, \n",
    "       c.name AS name_of_company, \n",
    "       fr.raised_amount AS amount \n",
    "FROM   investment AS i\n",
    "JOIN   c ON c.id = i.company_id\n",
    "JOIN   fund AS f ON f.id = i.fund_id\n",
    "JOIN   fr ON fr.id = i.funding_round_id;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da7077ec",
   "metadata": {},
   "source": [
    "| name_of_fund | name_of_company | amount |\n",
    "|---|---|---|\n",
    "| Advance Publication | Gigya | 1.53e+07 |\n",
    "| Mayfield Fund\t | Gigya | 1.53e+07 |\n",
    "| Benchmark | Gigya | 1.53e+07 |\n",
    "| DAG Ventures | Gigya | 1.53e+07 |\n",
    "| Mitsui Global Investment | OpenX | 2.50112e+07 |\n",
    "| Accel Partners | OpenX | 2.50112e+07 |\n",
    "| Presidio Ventures | OpenX | 2.50112e+07 |\n",
    "| Index Ventures | OpenX | 2.50112e+07 |\n",
    "| Samsung Ventures | OpenX | 2.50112e+07 |\n",
    "| SAP Ventures | OpenX | 2.50112e+07 |\n",
    "| Mayfield Fund | Gigya | 2.5e+07 |\n",
    "| Greenspring Associates | Gigya | 2.5e+07 |\n",
    "| Benchmark | Gigya | 2.5e+07 |\n",
    "| ... | ... | ... |"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5467ba9e",
   "metadata": {},
   "source": [
    "## Во сколько раз сумма покупки превысила сумму вложений"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ae3b8b74",
   "metadata": {},
   "source": [
    "Выгрузите таблицу, в которой будут такие поля:\n",
    "- название компании-покупателя;\n",
    "- сумма сделки;\n",
    "- название компании, которую купили;\n",
    "- сумма инвестиций, вложенных в купленную компанию;\n",
    "- доля, которая отображает, во сколько раз сумма покупки превысила сумму вложенных в компанию инвестиций, округлённая до ближайшего целого числа.\n",
    "\n",
    "Не учитывайте те сделки, в которых сумма покупки равна нулю. Если сумма инвестиций в компанию равна нулю, исключите такую компанию из таблицы. \n",
    "\n",
    "Отсортируйте таблицу по сумме сделки от большей к меньшей, а затем по названию купленной компании в лексикографическом порядке. Ограничьте таблицу первыми десятью записями."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d1600ee9",
   "metadata": {},
   "source": [
    "```sql\n",
    "WITH \n",
    "c2 AS (SELECT *\n",
    "       FROM company\n",
    "       WHERE funding_total > 0\n",
    ")\n",
    "\n",
    "SELECT c1.name AS acquiring_company_name, \n",
    "       a.price_amount, \n",
    "       c2.name AS acquired_company_name, \n",
    "       c2.funding_total, \n",
    "       ROUND(A.PRICE_AMOUNT / c2.funding_total)\n",
    "FROM   acquisition AS a\n",
    "LEFT JOIN company AS c1 ON c1.id = a.acquiring_company_id \n",
    "LEFT JOIN company AS c2 ON c2.id = a.acquired_company_id  \n",
    "WHERE  a.price_amount > 0\n",
    "AND    c2.funding_total > 0\n",
    "ORDER BY  a.price_amount DESC, \n",
    "          c2.name \n",
    "LIMIT     10;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c8f7185",
   "metadata": {},
   "source": [
    "| acquiring_company_name | price_amount | acquired_company_name | funding_total | round |\n",
    "|---|---|---|---|---|\n",
    "| Microsoft | 8.5e+09 | Skype | 7.6805e+07 | 111 |\n",
    "| Scout Labs | 4.9e+09 | Varian Semiconductor Equipment Associates | 4.8e+06 | 1021 |\n",
    "| Broadcom | 3.7e+09 | Aeluros | 7.97e+06 | 464 |\n",
    "| Broadcom | 3.7e+09 | NetLogic Microsystems | 1.88527e+08 | 20 |\n",
    "| Level 3 Communications | 3e+09 | Global Crossing | 4.1e+07 | 73 |\n",
    "| Yahoo! | 2.87e+09 | GeoCities | 4e+07 | 72 |\n",
    "| eBay | 2.6e+09 | Skype | 7.6805e+07 | 34 |\n",
    "| Salesforce | 2.5e+09 | ExactTarget | 2.3821e+08 | 10 |\n",
    "| Johnson & Johnson | 2.3e+09 | Crucell | 4.43e+08 | 5 |\n",
    "| IAC | 1.85e+09 | Ask.com | 2.5e+07 | 74 |"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "347e085d",
   "metadata": {},
   "source": [
    "## Компаний из категории `social`, получившие финансирование с 2010 по 2013 год"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9ad57df2",
   "metadata": {},
   "source": [
    "Выгрузите таблицу, в которую войдут названия компаний из категории `social`, получившие финансирование с 2010 по 2013 год включительно. Проверьте, что сумма инвестиций не равна нулю. Выведите также номер месяца, в котором проходил раунд финансирования."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fcf1695d",
   "metadata": {},
   "source": [
    "```sql\n",
    "WITH\n",
    "fr AS (SELECT company_id, \n",
    "              EXTRACT(MONTH FROM funded_at) AS funded_month\n",
    "       FROM   funding_round\n",
    "       WHERE  funded_at BETWEEN '2010-01-01' AND '2013-12-31'\n",
    "       AND    raised_amount > 0), \n",
    "\n",
    "c AS (SELECT id, \n",
    "             name\n",
    "      FROM   company\n",
    "      WHERE  category_code = 'social') \n",
    "\n",
    "SELECT c.name, \n",
    "       fr.funded_month\n",
    "FROM   c \n",
    "JOIN   fr ON fr.company_id = c.id \n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f2cb36f6",
   "metadata": {},
   "source": [
    "| name | funded_month |\n",
    "|---|---|\n",
    "| Klout | 1 |\n",
    "| WorkSimple | 3 |\n",
    "| HengZhi | 1 |\n",
    "| Network | 1 |\n",
    "| SocialGO | 1 |\n",
    "| ... | ... |"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dd2ddc15",
   "metadata": {},
   "source": [
    "## Данные об инвестиционных раундах по мусяцам"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8b9bb843",
   "metadata": {},
   "source": [
    "Отберите данные по месяцам с 2010 по 2013 год, когда проходили инвестиционные раунды. Сгруппируйте данные по номеру месяца и получите таблицу, в которой будут поля:\n",
    "- номер месяца, в котором проходили раунды;\n",
    "- количество уникальных названий фондов из США, которые инвестировали в этом месяце;\n",
    "- количество компаний, купленных за этот месяц;\n",
    "- общая сумма сделок по покупкам в этом месяце."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad219153",
   "metadata": {},
   "source": [
    "```sql\n",
    "WITH\n",
    "\n",
    "invest AS (SELECT EXTRACT(MONTH FROM fr.funded_at) AS funded_month, \n",
    "                  COUNT(DISTINCT f.id) AS count_fund\n",
    "           FROM   investment AS i \n",
    "           JOIN   funding_round AS fr ON fr.id = i.funding_round_id\n",
    "           JOIN   fund AS f ON f.id = i.fund_id\n",
    "           WHERE  f.country_code = 'USA'\n",
    "           AND    fr.funded_at BETWEEN '2010-01-01' AND '2013-12-31'\n",
    "           GROUP BY funded_month), \n",
    "\n",
    "acquired AS (SELECT EXTRACT(MONTH FROM acquired_at) AS acquired_month, \n",
    "                    COUNT(acquired_company_id) AS count_company, \n",
    "                    SUM(price_amount) AS sum_price_amount \n",
    "             FROM   acquisition\n",
    "             WHERE  acquired_at BETWEEN '2010-01-01' AND '2013-12-31'\n",
    "             GROUP BY acquired_month) \n",
    "\n",
    "SELECT invest.funded_month, \n",
    "       invest.count_fund, \n",
    "       acquired.count_company,\n",
    "       acquired.sum_price_amount\n",
    "FROM   invest\n",
    "JOIN   acquired ON acquired.acquired_month = invest.funded_month;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e773a671",
   "metadata": {},
   "source": [
    "| funded_month | count_fund | count_company | sum_price_amount |\n",
    "|---|---|---|---|\n",
    "| 1 | 815 | 600 | 2.71083e+10 |\n",
    "| 2 | 637 | 418 | 4.13903e+10 |\n",
    "| 3 | 695 | 458 | 5.95016e+10 |\n",
    "| 4 | 718 | 411 | 3.03837e+10 |\n",
    "| 5 | 695 | 532 | 8.60122e+10 |\n",
    "| 6 | 785 | 525 | 5.20883e+10 |\n",
    "| 7 | 803 | 488 | 4.98541e+10 |\n",
    "| 8 | 726 | 454 | 7.77093e+10 |\n",
    "| 9 | 793 | 491 | 6.97409e+10 |\n",
    "| 10 | 764 | 473 | 4.85567e+10 |\n",
    "| 11 | 661 | 414 | 4.79386e+10 |\n",
    "| 12 | 590 | 433 | 3.74251e+10 |"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "122007f6",
   "metadata": {},
   "source": [
    "## Сумма инвестиций в разные страны по годам"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1693d35f",
   "metadata": {},
   "source": [
    "Составьте сводную таблицу и выведите среднюю сумму инвестиций для стран, в которых есть стартапы, зарегистрированные в 2011, 2012 и 2013 годах. Данные за каждый год должны быть в отдельном поле. Отсортируйте таблицу по среднему значению инвестиций за 2011 год от большего к меньшему."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "810ee370",
   "metadata": {},
   "source": [
    "```sql\n",
    "WITH\n",
    "inv_2011 AS (SELECT co.country_code, \n",
    "                    AVG(co.funding_total) \n",
    "             FROM company AS co\n",
    "             WHERE EXTRACT(YEAR FROM co.founded_at) = 2011\n",
    "             GROUP BY co.country_code \n",
    "             HAVING COUNT(co.id) > 0), \n",
    "\n",
    "inv_2012 AS (SELECT co.country_code, \n",
    "                    AVG(co.funding_total) \n",
    "             FROM company AS co \n",
    "             WHERE EXTRACT(YEAR FROM co.founded_at) = 2012 \n",
    "             GROUP BY co.country_code \n",
    "             HAVING COUNT(co.id) > 0),\n",
    "\n",
    "inv_2013 AS (SELECT co.country_code, \n",
    "                    AVG(co.funding_total) \n",
    "             FROM company AS co \n",
    "             WHERE EXTRACT(YEAR FROM co.founded_at) = 2013 \n",
    "             GROUP BY co.country_code \n",
    "             HAVING COUNT(co.id) > 0)\n",
    "\n",
    "SELECT inv_2011.country_code,\n",
    "       inv_2011.avg AS inv_2011,\n",
    "       inv_2012.avg AS inv_2012,\n",
    "       inv_2013.avg AS inv_2013\n",
    "FROM inv_2011\n",
    "INNER JOIN inv_2012 ON inv_2012.country_code = inv_2011.country_code\n",
    "INNER JOIN inv_2013 ON inv_2013.country_code = inv_2011.country_code\n",
    "ORDER BY inv_2011.avg DESC;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bed179f3",
   "metadata": {},
   "source": [
    "| country_code | inv_2011 | inv_2012 | inv_2013 |\n",
    "|---|---|---|---|\n",
    "| PER | 4e+06 | 41000 | 25000 |\n",
    "| USA | 2.24396e+06 | 1.20671e+06 | 1.09336e+06 |\n",
    "| HKG | 2.18078e+06 | 226227 | 0 |\n",
    "| PHL | 1.75e+06 | 4218.75 | 2500 |\n",
    "| ARE | 1.718e+06 | 197222 | 35333.3 |\n",
    "| JPN | 1.66431e+06 | 674720 | 50000 |\n",
    "| AUT | 1.5342e+06 | 147806 | 85773.3 |\n",
    "| BRA | 1.38007e+06 | 240639 | 67944.4 |\n",
    "| DEU | 1.1288e+06 | 1.32915e+06 | 66612.7 |\n",
    "| ISR | 1.03076e+06 | 1.27121e+06 | 294022 |\n",
    "| PST | 1e+06 | 0 | 0 |\n",
    "| FRA | 977874 | 291227 | 642083 |\n",
    "| CHN | 975918 | 611436 | 1e+06 |\n",
    "| AUS | 963088 | 192949 | 26313.7 |\n",
    "| ZAF | 962000 | 576000 | 0 |\n",
    "| IRL | 929919 | 199051 | 36683.8 |\n",
    "| IND | 763426 | 230497 | 21814.4 |\n",
    "| RUS | 725870 | 420155 | 132500 |\n",
    "| GBR | 706840 | 357222 | 273237 |\n",
    "| TWN | 657143 | 110000 | 0 |\n",
    "| SWE | 629275 | 404111 | 95454.5 |\n",
    "| ARG | 590826 | 119327 | 4166.67 |\n",
    "| SGP | 580116 | 535380 | 147573 |\n",
    "| KOR | 576679 | 155875 | 352857 |\n",
    "| ESP | 530439 | 129098 | 27056.6 |\n",
    "| CAN | 514520 | 494707 | 114678 |\n",
    "| GRC | 489658 | 74841.3 | 7090.91 |\n",
    "| FIN | 474348 | 152907 | 78766.7 |\n",
    "| DNK | 427406 | 265956 | 121723 |\n",
    "| NOR | 400000 | 198552 | 60565.9 |\n",
    "| CHE | 351217 | 1.26955e+06 | 15217.4 |\n",
    "| ITA | 333191 | 242231 | 33886 |\n",
    "| IDN | 308696 | 89473.7 | 6250 |\n",
    "| ANT | 258333 | 0 | 9649.5 |\n",
    "| BEL | 258331 | 263657 | 53782.9 |\n",
    "| THA | 237500 | 33333.3 | 57142.9 |\n",
    "| HUN | 138654 | 465625 | 6750 |\n",
    "| LTU | 117512 | 102654 | 108333 |\n",
    "| TUR | 105088 | 397245 | 212500 |\n",
    "| EST | 96660.6 | 142956 | 7042.71 |\n",
    "| NLD | 94242.1 | 1.31927e+06 | 30870.6 |\n",
    "| MEX | 81947.1 | 4.03231e+06 | 73421.1 |\n",
    "| PRT | 77921.8 | 25555.6 | 0 |\n",
    "| UKR | 76384.6 | 26666.7 | 5277.78 |\n",
    "| KEN | 66666.7 | 18750 | 0 |\n",
    "| CZE | 64785.7 | 151364 | 0 |\n",
    "| LUX | 61839.3 | 0 | 0 |\n",
    "| CHL | 55263.2 | 90000 | 26909.1 |\n",
    "| BGR | 49345.3 | 59035.5 | 38174.1 |\n",
    "| COL | 49277.8 | 38100 | 0 |\n",
    "| GHA | 39872 | 0 | 0 |\n",
    "| ROM | 35905 | 17288.6 | 4117.65 |\n",
    "| JOR | 35333.3 | 0 | 0 |\n",
    "| MYS | 31157.9 | 6.13164e+07 | 0 |\n",
    "| POL | 16573.6 | 44205.1 | 20500 |\n",
    "| NZL | 14285.7 | 53333.3 | 0 |\n",
    "| EGY | 13461.5 | 31411.8 | 34200 |\n",
    "| BLR | 10000 | 0 | 0 |\n",
    "| ISL | 5885.17 | 24000 |  |\n",
    "| URY | 4000 | 175000 | 0 |\n",
    "| HRV | 4000 | 6502.17 | 0 |\n",
    "| VEN | 0 | 140000 | 12500 |\n",
    "| VNM | 0 | 9.09091e+06 | 0 |\n",
    "| MAR | 0 | 533333 | 0 |\n",
    "| BGD | 0 | 2333.33 | 0 |\n",
    "| ALB | 0 | 150000 | 0 |\n",
    "| MKD | 0 | 0 | 19242 |\n",
    "| NGA | 0 | 3.81438e+06 | 0 |\n",
    "| NPL | 0 | 0 | 0 |\n",
    "| PAK | 0 | 45454.5 | 0 |\n",
    "| LVA | 0 | 12500 | 0 |\n",
    "| CSS | 0 | 0 | 0 |\n",
    "| SAU | 0 | 0 | 0 |\n",
    "| CRI | 0 | 0 | 40000 |\n",
    "| SVN | 0 | 239616 | 0 |\n",
    "| BHR | 0 | 0 | 20000 |\n",
    "| UGA | 0 | 0 | 60400 |\n",
    "| LKA | 0 | 0 | 0 |"
   ]
  }
 ],
 "metadata": {
  "ExecuteTimeLog": [
   {
    "duration": 33199,
    "start_time": "2023-10-15T08:36:00.556Z"
   },
   {
    "duration": 202,
    "start_time": "2023-10-15T08:45:35.543Z"
   },
   {
    "duration": 1464,
    "start_time": "2023-10-15T08:45:35.906Z"
   },
   {
    "duration": 398,
    "start_time": "2023-10-15T08:45:37.372Z"
   },
   {
    "duration": 406,
    "start_time": "2023-10-15T08:50:00.281Z"
   },
   {
    "duration": 27386,
    "start_time": "2023-10-15T08:50:00.688Z"
   },
   {
    "duration": 1989,
    "start_time": "2023-10-15T08:50:28.076Z"
   },
   {
    "duration": 8189,
    "start_time": "2023-10-15T08:50:30.066Z"
   },
   {
    "duration": 4787,
    "start_time": "2023-10-15T08:50:38.258Z"
   },
   {
    "duration": 448,
    "start_time": "2023-10-15T09:20:15.774Z"
   },
   {
    "duration": 28717,
    "start_time": "2023-10-15T09:20:16.227Z"
   },
   {
    "duration": 1899,
    "start_time": "2023-10-15T09:20:44.946Z"
   },
   {
    "duration": 433,
    "start_time": "2023-10-15T09:20:46.846Z"
   },
   {
    "duration": 6519,
    "start_time": "2023-10-15T09:20:47.343Z"
   },
   {
    "duration": 6982,
    "start_time": "2023-10-15T09:20:53.863Z"
   },
   {
    "duration": 4413,
    "start_time": "2023-10-15T09:21:00.847Z"
   },
   {
    "duration": 2160,
    "start_time": "2023-10-15T09:28:12.432Z"
   },
   {
    "duration": 1655,
    "start_time": "2023-10-15T09:28:28.331Z"
   },
   {
    "duration": 786,
    "start_time": "2023-10-15T09:30:13.131Z"
   },
   {
    "duration": 431,
    "start_time": "2023-10-15T09:30:13.919Z"
   },
   {
    "duration": 214,
    "start_time": "2023-10-15T09:30:14.351Z"
   },
   {
    "duration": 425,
    "start_time": "2023-10-15T09:30:14.568Z"
   },
   {
    "duration": 1477,
    "start_time": "2023-10-15T09:30:14.995Z"
   },
   {
    "duration": 484,
    "start_time": "2023-10-15T09:30:23.562Z"
   },
   {
    "duration": 548,
    "start_time": "2023-10-15T09:33:39.600Z"
   },
   {
    "duration": 395,
    "start_time": "2023-10-15T09:33:40.152Z"
   },
   {
    "duration": 195,
    "start_time": "2023-10-15T09:33:40.549Z"
   },
   {
    "duration": 1311,
    "start_time": "2023-10-15T09:33:40.746Z"
   },
   {
    "duration": 1021,
    "start_time": "2023-10-15T09:33:42.059Z"
   },
   {
    "duration": 92,
    "start_time": "2023-10-15T09:37:44.140Z"
   },
   {
    "duration": 129,
    "start_time": "2023-10-15T09:37:54.232Z"
   },
   {
    "duration": 4651,
    "start_time": "2023-10-15T09:38:51.533Z"
   },
   {
    "duration": 3021,
    "start_time": "2023-10-15T09:39:12.563Z"
   },
   {
    "duration": 2230,
    "start_time": "2023-10-15T09:39:25.141Z"
   },
   {
    "duration": 2449,
    "start_time": "2023-10-15T09:39:57.935Z"
   },
   {
    "duration": 3018,
    "start_time": "2023-10-15T09:40:32.161Z"
   },
   {
    "duration": 2942,
    "start_time": "2023-10-15T09:43:36.953Z"
   },
   {
    "duration": 729,
    "start_time": "2023-10-15T09:43:39.897Z"
   },
   {
    "duration": 4063,
    "start_time": "2023-10-15T09:49:46.495Z"
   },
   {
    "duration": 114,
    "start_time": "2023-10-15T09:51:08.014Z"
   },
   {
    "duration": 3983,
    "start_time": "2023-10-15T09:51:21.973Z"
   },
   {
    "duration": 2676,
    "start_time": "2023-10-15T09:51:33.125Z"
   },
   {
    "duration": 3115,
    "start_time": "2023-10-15T09:52:56.836Z"
   },
   {
    "duration": 466,
    "start_time": "2023-10-15T10:00:16.768Z"
   },
   {
    "duration": 30138,
    "start_time": "2023-10-15T10:00:17.236Z"
   },
   {
    "duration": 1896,
    "start_time": "2023-10-15T10:00:47.376Z"
   },
   {
    "duration": 531,
    "start_time": "2023-10-15T10:00:49.345Z"
   },
   {
    "duration": 5189,
    "start_time": "2023-10-15T10:00:49.878Z"
   },
   {
    "duration": 3298,
    "start_time": "2023-10-15T10:00:55.069Z"
   },
   {
    "duration": 1476,
    "start_time": "2023-10-15T10:00:58.373Z"
   },
   {
    "duration": 5800,
    "start_time": "2023-10-15T10:00:59.851Z"
   },
   {
    "duration": 920,
    "start_time": "2023-10-15T10:01:05.653Z"
   },
   {
    "duration": 6889,
    "start_time": "2023-10-15T10:01:06.576Z"
   },
   {
    "duration": 5104,
    "start_time": "2023-10-15T10:01:13.466Z"
   },
   {
    "duration": 3270,
    "start_time": "2023-10-15T10:01:18.573Z"
   },
   {
    "duration": 3,
    "start_time": "2023-10-15T10:01:21.846Z"
   },
   {
    "duration": 1079,
    "start_time": "2023-10-15T10:02:27.477Z"
   },
   {
    "duration": 984,
    "start_time": "2023-10-15T10:02:40.677Z"
   },
   {
    "duration": 1024,
    "start_time": "2023-10-15T10:04:02.755Z"
   },
   {
    "duration": 847,
    "start_time": "2023-10-15T10:04:14.126Z"
   },
   {
    "duration": 987,
    "start_time": "2023-10-15T10:04:20.274Z"
   },
   {
    "duration": 436,
    "start_time": "2023-10-15T10:05:57.257Z"
   },
   {
    "duration": 27686,
    "start_time": "2023-10-15T10:05:57.696Z"
   },
   {
    "duration": 2027,
    "start_time": "2023-10-15T10:06:25.444Z"
   },
   {
    "duration": 490,
    "start_time": "2023-10-15T10:06:27.473Z"
   },
   {
    "duration": 4691,
    "start_time": "2023-10-15T10:06:27.966Z"
   },
   {
    "duration": 5595,
    "start_time": "2023-10-15T10:06:32.659Z"
   },
   {
    "duration": 2410,
    "start_time": "2023-10-15T10:06:38.256Z"
   },
   {
    "duration": 1586,
    "start_time": "2023-10-15T10:06:40.668Z"
   },
   {
    "duration": 1041,
    "start_time": "2023-10-15T10:06:42.258Z"
   },
   {
    "duration": 6313,
    "start_time": "2023-10-15T10:06:43.344Z"
   },
   {
    "duration": 4800,
    "start_time": "2023-10-15T10:06:49.660Z"
   },
   {
    "duration": 3393,
    "start_time": "2023-10-15T10:06:54.462Z"
   },
   {
    "duration": 112,
    "start_time": "2023-10-15T10:51:36.776Z"
   },
   {
    "duration": 9,
    "start_time": "2023-10-15T10:52:09.556Z"
   },
   {
    "duration": 8,
    "start_time": "2023-10-15T10:52:26.358Z"
   },
   {
    "duration": 10,
    "start_time": "2023-10-15T10:52:50.019Z"
   },
   {
    "duration": 8,
    "start_time": "2023-10-15T10:53:12.959Z"
   },
   {
    "duration": 8,
    "start_time": "2023-10-15T10:53:15.480Z"
   },
   {
    "duration": 451,
    "start_time": "2023-10-15T10:55:37.732Z"
   },
   {
    "duration": 24469,
    "start_time": "2023-10-15T10:55:38.185Z"
   },
   {
    "duration": 1618,
    "start_time": "2023-10-15T10:56:02.656Z"
   },
   {
    "duration": 493,
    "start_time": "2023-10-15T10:56:04.277Z"
   },
   {
    "duration": 3679,
    "start_time": "2023-10-15T10:56:04.772Z"
   },
   {
    "duration": 5002,
    "start_time": "2023-10-15T10:56:08.452Z"
   },
   {
    "duration": 2212,
    "start_time": "2023-10-15T10:56:13.456Z"
   },
   {
    "duration": 1178,
    "start_time": "2023-10-15T10:56:15.670Z"
   },
   {
    "duration": 909,
    "start_time": "2023-10-15T10:56:16.850Z"
   },
   {
    "duration": 4809,
    "start_time": "2023-10-15T10:56:17.762Z"
   },
   {
    "duration": 4100,
    "start_time": "2023-10-15T10:56:22.573Z"
   },
   {
    "duration": 2993,
    "start_time": "2023-10-15T10:56:26.675Z"
   },
   {
    "duration": 42,
    "start_time": "2023-10-15T11:20:23.596Z"
   },
   {
    "duration": 223,
    "start_time": "2023-10-15T18:47:05.731Z"
   }
  ],
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": true,
   "title_cell": "Содержание",
   "title_sidebar": "Contents",
   "toc_cell": true,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "263.594px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
